package mysqlCnnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class CatalogueUpdate extends ConnectionTOmysql 
{

	private Connection connection;

	/**
	 * This is the default constructor
	 * @throws IlegalMySQLPasswordORUserNameException 
	 */
	public CatalogueUpdate()
	{
		super();
		this.connection = super.getConnection();
	}
	
	/**
	 * This method insert product to data base
	 * @param price
	 * @param name
	 * @param description
	 * @param cataloguenumber
	 * @return true on success else false
	 */
	public boolean insertProduct(double price, String name,
			String description, long cataloguenumber)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO catalogue VALUES (?,?,?,?);");
			ps.setDouble(1, price);
			ps.setString(2, name);
			ps.setString(3, description);
			ps.setLong(4, cataloguenumber);			
			ps.executeUpdate();
			ps.close();
			return true;
		} catch (SQLException e)
			{
			 	return false;
			}
	}
	
	/**
	 * 
	 * @param price
	 * @param name
	 * @param description
	 * @param cataloguenumber
	 * @return
	 */
	public boolean updateProduct(double price, String name,
			String description, long cataloguenumber)
	{		
		try 
		{
			PreparedStatement ps = connection.prepareStatement("UPDATE catalogue SET price=?, name=?, description=? WHERE cataloguenumber=?;");
			ps.setDouble(1, price);
			ps.setString(2, name);
			ps.setString(3, description);
			ps.setLong(4, cataloguenumber);			
			ps.executeUpdate();
			ps.close();
			return true;
		} catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * 
	 * @param cataloguenum
	 * @return
	 */
	public String[] searchForProduct(long cataloguenum)
	{
		Statement stmt;
		String[] productTostr=null;
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM catalogue WHERE cataloguenumber = "+cataloguenum+";");
			productTostr = new String[4];
			if (rs.next())
			{	
				productTostr[0] = rs.getString(1);
				productTostr[1] = rs.getString(2);
				productTostr[2] = rs.getString(3);
				productTostr[3] = rs.getString(4);	
				rs.close();				
			}
			return productTostr;
		} catch (SQLException e)
		{
			return null;
		}
	}
	
	/**
	 * 
	 * @param catalogueID
	 * @return
	 */
	public String RemoveProduct(Long catalogueID)
	{
		try 
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM catalogue WHERE cataloguenumber=?;");
			ps.setDouble(1, catalogueID);
			ps.executeUpdate();
			ps.close();
			return "OK";
		} catch (SQLException e)
		{
		 	return "ERROR";
		}
	}
	
	/**
	 * 
	 * @return array of Long of products ID
	 */
	public long[] loadAllProductID()
	{
		Statement stmt;
		long[] productsID = null;
		int len=1, i=0;
		Vector<Integer> VecproductID=new Vector<Integer>();
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT cataloguenumber FROM catalogue;");
			while(rs.next())
			{
				VecproductID.add(rs.getInt(1));
			}	
			len = VecproductID.size();
			productsID = new long[len];
			for (i=0; i < len; i++)
				productsID[i] = VecproductID.get(i);
			
			rs.close();
			return productsID;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * 
	 * @return array of String of all sections 
	 */
	public String[] loadAllProductsNames()
	{
		Statement stmt;
		String[] productName = null;
		int len=1, i=0;
		Vector<String> sectionVec=new Vector<String>();
		String sqlmsg = String.format("SELECT * FROM catalogue;");
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				sectionVec.add(rs.getString(4)+": "+rs.getString(2));
			}	
			len = sectionVec.size();
			productName = new String[len];
			for (i=0; i < len; i++)
				productName[i] = sectionVec.get(i);
			
			rs.close();
			return productName;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	/**
	 * 
	 * @return array of String of all sections product
	 */
	public String[] loadSectionsProduct(int sectionID)
	{
		Statement stmt;
		String[] products = null;
		int len=1, i=0;
		Vector<String> Vecproduct=new Vector<String>();
		String sqlmsg = String.format("SELECT C.cataloguenumber, C.description ,C.name " +
				",C.price FROM catalogue AS C, sectype AS S, productstype AS P WHERE " +
				"S.SectionID1 = %d AND S.typeID1 = P.typeID AND " +
				"P.cataloguenumber = C.cataloguenumber;", sectionID);
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				Vecproduct.add(String.format("PID: %-15s Description: %-30s  " +
						"Name: %-20s Price: %-10s", rs.getString(1),rs.getString(2),
						rs.getString(3),rs.getString(4)));
			}	
			len = Vecproduct.size();
			products = new String[len];
			for (i=0; i < len; i++)
				products[i] = Vecproduct.get(i);
			
			rs.close();
			return products;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * 
	 * @return array of String of all sections 
	 */
	public String[] loadAllCategorySections()
	{
		Statement stmt;
		String[] sections = null;
		int len=1, i=0;
		Vector<String> sectionVec=new Vector<String>();
		String sqlmsg = String.format("SELECT * FROM sections;");
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				sectionVec.add(rs.getString(1)+":"+rs.getString(2));
			}	
			len = sectionVec.size();
			sections = new String[len];
			for (i=0; i < len; i++)
				sections[i] = sectionVec.get(i);
			
			rs.close();
			return sections;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * This method add section to data base : insert into table sections.
	 * @param sectionID
	 * @param sectionName
	 * @return true on success , false on error.
	 */
	public boolean addCatalogueSection(int sectionID, String sectionName)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO sections VALUES (?,?);");
			ps.setInt(1, sectionID);
			ps.setString(2, sectionName);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This method update section details in data base : update table sections.
	 * @param sectionID
	 * @param sectionName
	 * @return true on success , false on error.
	 */
	public boolean updateCatalogueSection(int sectionID, String sectionName)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("UPDATE sections SET SectionName=? WHERE SectionID=?;");
			ps.setString(1, sectionName);
			ps.setInt(2, sectionID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This method remove section details from data base : remove from table sections.
	 * @param sectionID
	 * @return true on success , false on error.
	 */
	public boolean removeCatalogueSection(int sectionID)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM sections WHERE SectionID=?;");
			ps.setInt(1, sectionID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This method remove section details from data base : remove from table sections.
	 * @param sectionID
	 * @return true on success , false on error.
	 */
	public boolean removeCatalogueType(int typeID)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM type WHERE typeID=?;");
			ps.setInt(1, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This method update type details in data base : update table type.
	 * @param typeID
	 * @param typeName
	 * @return true on success , false on error.
	 */
	public boolean updateCatalogueType(int typeID, String typeName)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("UPDATE type SET typeName=? WHERE typeID=?;");
			ps.setString(1, typeName);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This method add type details to data base : update table type.
	 * @param typeID
	 * @param typeName
	 * @return true on success , false on error.
	 */
	public boolean addCataloguetype(int typeID, String typeName)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO type VALUES (?,?);");
			ps.setInt(1, typeID);
			ps.setString(2, typeName);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * 
	 * @return array of String of all sections 
	 */
	public String[] loadAllCategoryType()
	{
		Statement stmt;
		String[] type = null;
		int len=1, i=0;
		Vector<String> typeVec=new Vector<String>();
		String sqlmsg = String.format("SELECT * FROM type;");
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				typeVec.add(rs.getString(1)+":"+rs.getString(2));
			}	
			len = typeVec.size();
			type = new String[len];
			for (i=0; i < len; i++)
				type[i] = typeVec.get(i);
			
			rs.close();
			return type;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * 
	 * @return array of String of all type product
	 */
	public String[] loadTypeProduct(int typeID)
	{
		Statement stmt;
		String[] products = null;
		int len=1, i=0;
		Vector<String> Vecproduct=new Vector<String>();
		String sqlmsg = String.format("SELECT C.cataloguenumber, C.description ,C.name " +
				",C.price FROM catalogue AS C, productstype AS P WHERE " +
				"P.typeID = %d AND P.cataloguenumber = C.cataloguenumber;", typeID);
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				Vecproduct.add(String.format("PID: %-15s Description: %-30s  " +
						"Name: %-20s Price: %-10s", rs.getString(1),rs.getString(2),
						rs.getString(3),rs.getString(4)));
			}	
			len = Vecproduct.size();
			products = new String[len];
			for (i=0; i < len; i++)
				products[i] = Vecproduct.get(i);
			
			rs.close();
			return products;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * This method remove product from: update table productstype
	 * @param typeID The type number
	 * @param productID the product id to remove from type
	 * @return true on success , false on error
	 */
	public boolean removeProductFromType(int typeID, long productID)
	{		 
		try
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM productstype WHERE cataloguenumber=? AND typeID=?;");
			ps.setLong(1, productID);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This method add product to type: update table productstype
	 * @param typeID The type number
	 * @param productID the product id to add to the type
	 * @return true on success , false on error
	 */
	public boolean addProductToType(int typeID, long productID)
	{		 
		try
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO productstype VALUES (?,?);");
			ps.setLong(1, productID);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * 
	 * @return array of String of all type product
	 */
	public String[] loadAllSectionType(int cectionID)
	{
		Statement stmt;
		String[] types = null;
		int len=1, i=0;
		Vector<String> typeVec=new Vector<String>();
		String sqlmsg = String.format("SELECT T.typeID, T.typeName FROM " +
				"type AS T, sectype AS S WHERE " +
				"S.SectionID1 = %d AND S.typeID1 = T.typeID;", cectionID);
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				typeVec.add(String.format("%s:%s",
						rs.getString(1),rs.getString(2)));
			}	
			len = typeVec.size();
			types = new String[len];
			for (i=0; i < len; i++)
				types[i] = typeVec.get(i);			
			rs.close();
			return types;
		} 
		catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * This method remove product from: update table productstype
	 * @param typeID The type number
	 * @param productID the product id to remove from type
	 * @return true on success , false on error
	 */
	public boolean removeTypeFromSection(int cectionID, int typeID)
	{		 
		try
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM sectype " +
					"WHERE SectionID1=? AND typeID1=?;");
			ps.setLong(1, cectionID);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This method add type to section: update table sectype
	 * @param sectionID The section number 
	 * @param typeID The type ID to add to section
	 * @return true on success , false on error
	 */
	public boolean addTypeToSection(int sectionID, int typeID)
	{		 
		try
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO sectype VALUES (?,?);");
			ps.setLong(1, sectionID);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
}
